﻿using System;
using System.Collections.Generic;
using System.Text;

namespace WFENGINE
{
    public static class EngineSql
    {
        #region PRO_ENGINEII_START_APPLY_NCOM
        public const string PRO_ENGINEII_START_APPLY_NCOM = @"
            -- Created on 2013/4/26 by ADMINISTRATOR 
            declare 
              -- Local variables here
              p_ApplyID  wf_apply.applyid%type:='10';
              v_Fact_no    wf_apply.fact_no%type;
              v_prcstepid1 wf_prcsteps.prcstepid%type;
              v_applier    wf_apply.applier%type;
              v_applier1   wf_apply.applier%type;
              v_level      wf_prcsteps.ilevel%type;
              v_pos        number := 0;
              v_pos1       number := 1;
              v_starttime  date;
              v_formruleid     wf_signrecord.Formruleid%TYPE;
              --For Cursor Variables
              v_prcstepid      wf_prcsteps.prcstepid%type;
              v_processid      wf_prcsteps.processid%type;
              v_stepname       wf_prcsteps.stepname%type;
              v_stepusers      varchar2(400);
              v_ilevel         wf_prcsteps.ilevel%type;
              v_signtype       varchar2(50):='1';--prcstepsetting.signtype%type;
              v_apcount        number;
              v_signrecordid   wf_signrecord.signrecordid%type;
              v_signorder      wf_signrecord.signorder%type;
              v_signmode       wf_signrecord.signmode%type;
              v_modisigner     wf_prcsteps.Modisigner%TYPE;
              v_Ndisplay       wf_prcsteps.ndisplay%TYPE;
              v_ylevel         wf_signrecord.ylevel%TYPE;
              v_subprcids      wf_prcsteps.subprcids%TYPE;--設定的子流程IDs
              v_substart       wf_prcsteps.substart%TYPE;--是否子流程啟動點
              v_subprocessid   NUMBER;--子流程ID
              v_subprocesscode wf_processes.processcode%TYPE;
              v_pare_signrecordid NUMBER;
              cursor cur is--取得具體的簽核流程
             select pss.prcstepid,pss.processid,pss.stepname,fun_get_step_users(pss.prcstepid),ROWNUM as ilevel,
                      formruleid,Modisigner,Ndisplay,PSS.MUSTSIGN,PSS.YLEVEL,pss.subprcids,pss.substart,
                      '1' as SIGNTYPE
               from wf_prcsteps pss
                   start with pss.prcstepid=v_prcstepid1
                   connect by pss.prcstepid =prior pss.parestepid; 
              SUBCUR PKG_TRANS.refCURSOR;
              V_LOGIN_NAME VARCHAR2(30);
              V_USER_NAME  VARCHAR2(30);
              V_APPCOUNT   VARCHAR2(30);
              OUT_RESULT   VARCHAR2(200);
              OUT_SUB_RESULT VARCHAR2(200);
              V_MUSTSIGN CHAR(1);
              v_JUMPSTART wf_PROCESSES.JUMPSTART%TYPE;
              v_del_nodes VARCHAR2(200):=' ';
              v_del_ns_ary pkg_util.str_array;
              v_del_node  VARCHAR2(50);
            begin

            --取得申請人流程啟動點
            SELECT fact_no,processid,applier,
                   Fun_Get_Applierprcstepid_Con(processid,applier,P_APPLYID)
              INTO v_Fact_no,v_processid,v_applier,v_prcstepid1
              FROM wf_apply where applyid=p_ApplyID;

            --取得申請人信息
            SELECT UM.LOGIN_NAME,UM.USER_NAME,PR.APPCOUNT
              INTO V_LOGIN_NAME,V_USER_NAME,V_APPCOUNT
              FROM wf_USERM UM,wf_PROCESSINGUSER PR,wf_APPLY AP
             WHERE AP.APPLIER=PR.USERID(+) AND RTRIM(UM.USER_ID)=AP.APPLIER AND
                   AP.APPLYID=P_APPLYID AND AP.FACT_NO=PR.FACTNO(+) AND rownum=1;

            if v_prcstepid1 IN(0,-1) then
               Raise_application_error(-20000,
                     'EWF系統建置便用者流程錯誤:使用者('||V_USER_NAME||'-ERP帳號:'||v_appcount||
                     ')的流程未建立!('||v_Fact_no||','||v_processid||','||v_applier||')');
            end if;

            UPDATE wf_apply a
               SET a.cursigner = v_applier,a.cursignernm = V_USER_NAME,
                   a.appliername = V_USER_NAME
             WHERE applyid=p_ApplyID;

            SELECT ilevel+1 into v_level
              FROM wf_prcsteps where prcstepid=v_prcstepid1;

            OPEN CUR;
            lOOP
                FETCH CUR INTO v_prcstepid,v_processid,v_stepname,v_stepusers,v_ilevel,
                      v_formruleid,v_modisigner,v_Ndisplay,V_MUSTSIGN,v_ylevel,v_subprcids,
                      v_substart,v_signtype;
                EXIT WHEN CUR%NOTFOUND;
                v_pos := 0;
                v_pos1:= 1;  --jjpfly
                v_starttime := null;
                if v_ilevel=1 then
                   v_stepusers := v_applier;
                   v_starttime := sysdate;
                end if;

                
                  if instr(v_stepusers,',')>0 then
                      if substr(v_stepusers,length(v_stepusers))<>',' then
                         v_stepusers := v_stepusers||',';
                      end if;
                      While instr(v_stepusers,',',v_pos+3)>0 Loop
                          v_pos1 := instr(v_stepusers,',',v_pos+1);
                          v_applier1 := substr(v_stepusers,v_pos+1,v_pos1-v_pos-1);
                          v_pos := v_pos1;
                          insert into wf_signrecord(fact_no,processid,prcstepid,applyid,signatory,
                                 starttime,isincrement,isredirect,back_mk,stepname,signorder,
                                 signmode,result,formruleid,Modisigner,ndisplay,MUSTSIGN,
                                 YLEVEL,signbefore,substart)
                         values
                                (v_Fact_no,v_processid,v_prcstepid,p_ApplyID,rtrim(v_applier1),
                                v_starttime,'N','N','N',v_stepname,v_ilevel,v_signtype,
                                decode(v_ilevel,'1','A','N'),v_formruleid,v_modisigner,v_Ndisplay,
                                V_MUSTSIGN,v_ylevel,'N',v_substart);
                      End Loop;
                  else
                      insert into wf_signrecord(fact_no,processid,prcstepid,applyid,signatory,
                             starttime,isincrement,isredirect,back_mk,stepname,signorder,
                             signmode,result,formruleid,Modisigner,ndisplay,MUSTSIGN,
                             YLEVEL,signbefore,substart)
                     values
                            (v_Fact_no,v_processid,v_prcstepid,p_ApplyID,rtrim(v_stepusers),
                            v_starttime,'N','N','N',v_stepname,v_ilevel,v_signtype,
                            decode(v_ilevel,'1','A','N'),v_formruleid,v_modisigner,v_Ndisplay,
                            V_MUSTSIGN,v_ylevel,'N',v_substart);
                  end if;

            END LOOP;
            CLOSE CUR;
            --Commit;
            --增加處理需刪除的簽核點 2012-4-6 Hou 主要針對子流程啟動點需刪除的情況
            IF v_del_nodes<>' ' THEN
               v_del_nodes := substr(v_del_nodes,2);
               v_del_ns_ary := pkg_util.split(v_del_nodes, ',');
               FOR i in v_del_ns_ary.first .. v_del_ns_ary.last LOOP
                   v_del_node := v_del_ns_ary(i);
                   SELECT signorder INTO v_signorder FROM wf_signrecord WHERE applyid=p_ApplyID AND signrecordid=v_del_node;
                   DELETE wf_signrecord WHERE applyid=p_ApplyID AND signrecordid=v_del_node;
                   UPDATE wf_signrecord SET signorder=signorder-1 WHERE applyid=p_ApplyID AND signorder>v_signorder;
               END LOOP;
            END IF;

            Update wf_signrecord sr set sguid=rowid,username=fun_get_username(signatory) where applyid=p_ApplyID;

            --2011-8-1 Hou 處理有的流程因設置問題導致申請人的SignOrder<>1的情況
            SELECT MIN(signorder) INTO v_signorder 
              FROM wf_signrecord
             WHERE applyid=p_ApplyID;
            IF v_signorder<>1 THEN
               v_pos := v_signorder-1;
               UPDATE wf_signrecord SET signorder=signorder-v_pos
                WHERE applyid=p_ApplyID;
            END IF;

            SELECT JUMPSTART INTO v_JUMPSTART FROM wf_APPLY AP,wf_PROCESSES P
            WHERE AP.PROCESSID=P.PROCESSID AND AP.APPLYID=p_ApplyID;

            select count(*) into v_apcount from wf_apply ap,wf_signrecord sr
            where ap.applyid=sr.applyid and ap.applier=sr.signatory
                  and ap.applyid=p_ApplyID and sr.signorder>1;

            if v_apcount>0 then
               select signrecordid,signorder,signmode into v_signrecordid,v_signorder,v_signmode
               from wf_apply ap,wf_signrecord sr
               where ap.applyid=sr.applyid and ap.applier=sr.signatory
                     and ap.applyid=p_ApplyID and sr.signorder>1;
               IF v_JUMPSTART='Y' THEN
               --當申請人也是簽核人且PROCESS.JUMPSTART=Y,則刪除申請人在簽核位置以下的簽核點. 2010-6-28 FANS
                 DELETE wf_SIGNRECORD WHERE APPLYID=p_ApplyID AND SIGNORDER>1 AND SIGNORDER<=v_signorder;
                 UPDATE wf_SIGNRECORD SET SIGNORDER=SIGNORDER-V_SIGNORDER+1
                  WHERE APPLYID=p_ApplyID AND SIGNORDER>1;
               END IF;

            End if;
            --commit;
              --執行條件流程分析
              PKG_CONDITION_PROCESS.PRO_APPLY_CONDITIONS(P_APPLYID,OUT_RESULT);
              IF OUT_RESULT<>'Y' THEN
                 Raise_application_error(-20000, OUT_RESULT);
              END IF;

            Exception
            when others then
                 IF OUT_RESULT IS NOT NULL AND OUT_RESULT<>'Y' THEN
                     Raise_application_error(-20000, OUT_RESULT);
                 ELSE
                     Raise_application_error(-20000, 'EWF建置使用者流程錯誤!'||sqlerrm);
                 END IF;
            end;";
        #endregion

        #region PRO_ENGINE_MOVESIGNS_ALL
        public const string PRO_ENGINE_MOVESIGNS_ALL = @"
            -- Created on 2013/4/26 by ADMINISTRATOR 
            declare 
              -- Local variables here
              i integer;
              p_ApplyID  wf_apply.applyid%type:={0};
            begin
              Insert into  wf_Signrecord_HIS(Signrecordid,Fact_No,Processid,Prcstepid,Applyid,
                     Signatory,Actsignatory,Starttime,Endtime,Isincrement,Isredirect,Back_Mk,
                     Result,Signfilepath,Memo,memo_ps,Signtype,Grantee,Datachgtime,Stepname,Signorder,
                     Signmode,Isrealsign,Ip,Mac,Sguid,Username,Formruleid,Modisigner,Ndisplay,Mustsign,
                     YLEVEL,Paresignrecordid,Signbefore,Substart,Userconmids,Readmk,Wf_Code,
                     Nodetype,Remoteid,Aud_Code,Actsigner_Positnm,Step_Action)
              select Signrecordid,Fact_No,Processid,Prcstepid,Applyid,
                     Signatory,Actsignatory,Starttime,Endtime,Isincrement,Isredirect,Back_Mk,
                     Result,Signfilepath,Memo,memo_ps,Signtype,Grantee,sysdate,Stepname,Signorder,
                     Signmode,'1',Ip,Mac,Sguid,Username,Formruleid,Modisigner,Ndisplay,Mustsign,
                     YLEVEL,Paresignrecordid,Signbefore,Substart,Userconmids,Readmk,Wf_Code,
                     Nodetype,Remoteid,Aud_Code,Actsigner_Positnm,Step_Action
                from wf_Signrecord where applyid=p_ApplyID;

              Delete from  wf_Signrecord where applyid = p_ApplyID;

              --Commit;
              Exception
              When Others then
                   Rollback;
                   raise_application_error(-20000,'搬移全部單據錯誤'||Sqlerrm);
            end;
            ";
        #endregion

        #region PRO_ENGINE_MOVESIGNS_PART
        public const string PRO_ENGINE_MOVESIGNS_PART = @"
            -- Created on 2013/4/26 by ADMINISTRATOR 
            declare 
              -- Local variables here
             p_ApplyID    wf_apply.applyid%type:={0};--退簽的單據ID
             p_ProcessId  NUMBER:={1};--退簽至簽核點的流程Id
             p_StartOrder wf_signrecord.signorder%type:={2};
             p_EndOrder   wf_signrecord.signorder%type:={3};
             v_StartOrder wf_signrecord.signorder%type;
             v_EndOrder   wf_signrecord.signorder%type;
             v_chgtime    DATE := SYSDATE;
            begin

            if p_StartOrder>p_EndOrder then
               v_StartOrder := p_EndOrder;
               v_EndOrder   := p_StartOrder;
            else
               v_StartOrder := p_StartOrder;
               v_EndOrder   := p_EndOrder;
            end if;


              --移動子流程,子流程不會再有子流程
              Insert into wf_Signrecord_HIS(Signrecordid,Fact_No,Processid,Prcstepid,Applyid,
                     Signatory,Actsignatory,Starttime,Endtime,Isincrement,Isredirect,Back_Mk,
                     Result,Signfilepath,Memo,Signtype,Grantee,Datachgtime,Stepname,Signorder,
                     Signmode,Ip,Mac,Sguid,Username,Formruleid,Modisigner,Ndisplay,Mustsign,
                     YLEVEL,Paresignrecordid,Signbefore,Substart,Userconmids,Readmk,Wf_Code,
                     Nodetype,Remoteid,Aud_Code,Actsigner_Positnm,Step_Action)
              select Signrecordid,Fact_No,Processid,Prcstepid,Applyid,
                     Signatory,Actsignatory,Starttime,Endtime,Isincrement,Isredirect,Back_Mk,
                     Result,Signfilepath,Memo,Signtype,Grantee,v_chgtime,Stepname,Signorder,
                     Signmode,Ip,Mac,Sguid,username,Formruleid,Modisigner,Ndisplay,Mustsign,
                     YLEVEL,Paresignrecordid,Signbefore,Substart,Userconmids,Readmk,Wf_Code,
                     Nodetype,Remoteid,Aud_Code,Actsigner_Positnm,Step_Action
                FROM wf_signrecord sr
               WHERE applyid=p_ApplyID AND paresignrecordid IN(
                    SELECT signrecordid
                      FROM wf_Signrecord
                     WHERE applyid=p_ApplyID AND processid=p_ProcessId and
                           endtime is not null and signorder between v_StartOrder and
                           v_EndOrder AND substart='Y'
                      );
              --刪除子流程
              DELETE wf_signrecord
               WHERE applyid=p_ApplyID AND paresignrecordid IN(
                    SELECT signrecordid
                      FROM wf_Signrecord
                     WHERE applyid=p_ApplyID AND processid=p_ProcessId and
                           endtime is not null and signorder between v_StartOrder and
                           v_EndOrder AND substart='Y'
                      );
              --移動主流程
              Insert into wf_Signrecord_HIS(Signrecordid,Fact_No,Processid,Prcstepid,Applyid,
                     Signatory,Actsignatory,Starttime,Endtime,Isincrement,Isredirect,Back_Mk,
                     Result,Signfilepath,Memo,memo_ps,Signtype,Grantee,Datachgtime,Stepname,Signorder,
                     Signmode,Ip,Mac,Sguid,Username,Formruleid,Modisigner,Ndisplay,Mustsign,
                     YLEVEL,Paresignrecordid,Signbefore,Substart,Userconmids,Readmk,Wf_Code,
                     Nodetype,Remoteid,Aud_Code,Actsigner_Positnm)
              select Signrecordid,Fact_No,Processid,Prcstepid,Applyid,
                     Signatory,Actsignatory,Starttime,Endtime,Isincrement,Isredirect,Back_Mk,
                     Result,Signfilepath,Memo,memo_ps,Signtype,Grantee,v_chgtime,Stepname,Signorder,
                     Signmode,Ip,Mac,Sguid,username,Formruleid,Modisigner,Ndisplay,Mustsign,
                     YLEVEL,Paresignrecordid,Signbefore,Substart,Userconmids,Readmk,Wf_Code,
                     Nodetype,Remoteid,Aud_Code,Actsigner_Positnm
                from wf_Signrecord
               where applyid=p_ApplyID AND processid=p_ProcessId and
                     endtime is not null and signorder between v_StartOrder and v_EndOrder;

              DELETE FROM wf_Signrecord
               WHERE applyid = p_ApplyID and signorder between v_StartOrder and
                     v_EndOrder and endtime is not NULL AND processid=p_ProcessId;
              Commit;
              Exception
              When Others then
                   Rollback;
                   raise_application_error(-20000,'搬移部分單據錯誤'||Sqlerrm);
            end;
            ";
        #endregion

        #region PRO_ENGINE_SIGNAPPROVE
        public const string PRO_ENGINE_SIGNAPPROVE = @"
        
        -- Created on 2013/4/27 by ADMINISTRATOR 
        declare 
          -- Local variables here
           -- Test statements here
          p_SignRecordID   WF_SIGNRECORD.SIGNRECORDID%TYPE:={0};--  當前簽核記錄ID
          p_ApplyID   WF_APPLY.APPLYID%TYPE:={1};--  當前單據ID
          P_PRCSTEPID  WF_SIGNRECORD.PRCSTEPID%TYPE:='{2}'; --節點ID
          p_UserID   WF_APPLY.APPLiER%TYPE:='{3}';--  當前用戶ID
          p_SignType   WF_SIGNRECORD.SIGNTYPE%TYPE:='{4}';--  當前用戶對單據的簽核類型，其值如下：1：自己簽核： 2：代理人簽核：3：授權人簽核：
          p_SignFilePath   WF_SIGNRECORD.SIGNFILEPATH%TYPE:='{5}';--  簽名檔
          P_PRIORITY       VARCHAR2(50):='{6}';--優先級別
          p_Memo   WF_SIGNRECORD.MEMO%TYPE:='{7}';--  備注
          p_UserIP   WF_SIGNRECORD.IP%TYPE:='{8}';--  用戶IP
          p_UserMAC   WF_SIGNRECORD.MAC%TYPE:='{9}';--  用戶MAC
          P_OTHERUSERID  WF_APPLY.APPLIER%TYPE:='{10}';--會簽、轉呈、覆核人員
          P_BACKSIGNSTEPID  WF_SIGNRECORD.PRCSTEPID%TYPE:='{11}';--退簽點
          P_STEPNAME        WF_SIGNRECORD.STEPNAME%TYPE:='{12}';--轉呈、覆核節點名稱
          p_ExecResult   VARCHAR(50); --  傳出參數，執行成功返回Y，失敗或發生異常返回N
          v_currentstatus     WF_APPLY.CURRENTSTATUS%TYPE;
          v_newstatus     WF_APPLY.CURRENTSTATUS%TYPE;
          v_processid         WF_apply.processid%TYPE;
          v_signmode          WF_SIGNRECORD.SIGNMODE%TYPE;
          v_result            WF_SIGNRECORD.RESULT%TYPE;
          v_signfinish        INTEGER;
          v_secright          VARCHAR2(1);
          v_together_count    NUMBER;
          v_cur_signers       VARCHAR2(4000);--當前簽核人id,id,id#name,name,name
          V_PRIORITY          VARCHAR2(10);
          v_prcstepid WF_signrecord.prcstepid%TYPE;
          v_processcode WF_apply.processcode%TYPE;
          v_SignFilePath WF_signrecord.signfilepath%type := fun_get_signfilepath(p_UserID,p_SignFilePath);       --用戶簽名
          v_fg_newstyle WF_apply.key7%TYPE;
          v_cur_uid   WF_signrecord.signatory%TYPE;
          v_flow      NUMBER;
          v_error VARCHAR2(500);
          v_srid NUMBER;
          vapplier WF_apply.applier%TYPE;
          vpicinfo VARCHAR2(200);
          v_left   NUMBER;
          v_top    NUMBER;
          v_cursignersub VARCHAR2(2000);
          v_cursignernamesub VARCHAR2(4000);
          v_fg_script VARCHAR2(500);
          V_FACT_NO           WF_APPLY.FACT_NO%TYPE;
          V_KEY2              WF_APPLY.KEY2%TYPE;
          v_substart          WF_signrecord.substart%TYPE;
          v_paresignrecordid  WF_signrecord.paresignrecordid%TYPE;
          out_result          VARCHAR2(300);
          v_count             NUMBER;
          v_count1            NUMBER;
          subrefcur           pkg_trans.refCURSOR;
          v_SignRecordID2     NUMBER;
          v_sub_srid          NUMBER;
          V_SUB_PROCESSID     NUMBER;
          v_controls          WF_apply.controls%TYPE;
          v_count_substart    NUMBER;
        begin
         
        BEGIN
          IF p_signrecordId=-9 THEN--申請人送審
             SELECT signrecordid INTO v_srid
               FROM WF_signrecord WHERE applyid=p_applyid AND signorder=1 AND result='A';
          ELSE
             v_srid := p_signrecordId;
          END IF;
          SELECT FACT_NO,KEY2,processid,currentstatus,processcode,applier
          INTO v_fact_no,v_key2,v_processid,v_currentstatus,v_processcode,vapplier
          FROM WF_apply WHERE applyid=p_applyid;

          SELECT FACT_NO,KEY2,processid,currentstatus,processcode,applier
          INTO v_fact_no,v_key2,v_processid,v_currentstatus,v_processcode,vapplier
          FROM WF_apply WHERE applyid=p_applyid;

          IF v_currentstatus='300.003' THEN
             p_ExecResult :='Y';
             RETURN;
          END IF;

          v_secright := fun_check_op_rights('',v_srid,p_UserID);
          IF v_secright='0' THEN
             p_ExecResult := 'N';
         
             raise_application_error(-20000,'簽核發生錯誤:您沒有權限執行此動作,請刷新簽核列表后重試!!');
             RETURN;
          END IF;
          if v_currentstatus='300.001' THEN
             --檢查是否有沒有子流程記錄的子流程啟動點
             --若有,則立即刪除該啟動點!
             SELECT COUNT(*) INTO v_count
               FROM WF_signrecord sr,(
                    SELECT paresignrecordid,COUNT(signrecordid) AS num
                      FROM WF_signrecord
                     WHERE applyid=p_applyid AND paresignrecordid<>-1
                     GROUP BY paresignrecordid
                    ) sr2
              WHERE sr.signrecordid=sr2.paresignrecordid(+) AND
                    sr.applyid=p_ApplyID AND sr.substart='Y' AND
                    nvl(sr2.num,0)=0;
             IF v_count>0 THEN
               DELETE WF_signrecord
                WHERE applyid=p_ApplyID AND signrecordid IN(
                   SELECT sr.signrecordid
                     FROM WF_signrecord sr,(
                          SELECT paresignrecordid,COUNT(signrecordid) AS num
                            FROM WF_signrecord
                           WHERE applyid=p_applyid AND paresignrecordid<>-1
                           GROUP BY paresignrecordid
                          ) sr2
                    WHERE sr.signrecordid=sr2.paresignrecordid(+) AND
                          sr.applyid=p_ApplyID AND sr.substart='Y' AND
                          nvl(sr2.num,0)=0
               );
             END IF;
             
          END IF;
           
          v_newstatus := v_currentstatus;
          IF v_processcode='FW' THEN
             SELECT COUNT(*) INTO v_flow FROM WF_signrecord WHERE applyid=p_applyid;
             IF v_flow<=1 THEN
                raise_application_error(-20000,'送簽發生錯誤:該單據尚未設置簽核流程，請確定後重試!');
             END IF;
          END IF;
          SELECT COUNT(*) INTO v_flow FROM WF_signrecord WHERE applyid=p_applyid AND modisigner='Y';
           IF v_flow>0 THEN
             ROLLBACK; 
             p_ExecResult := 'N';
      
                    raise_application_error(-20000,'送簽發生錯誤:該單據有部分簽核人員需設置，請點擊【設定流程】處理重試!');
              --COMMIT;
              RETURN;
           END IF;

          SELECT signatory,processid,prcstepid,substart,paresignrecordid
            INTO v_cur_uid,v_processid,v_prcstepid,v_substart,v_paresignrecordid
          FROM WF_signrecord s WHERE signrecordid = v_srid;
     

          --授權人簽核
          IF p_SignType IN ('3') THEN
            v_SignFilePath := Fun_Get_SignFilePath_Granter(v_srid,p_UserID);
            UPDATE WF_SignRecord sr SET sr.endtime=SYSDATE,sr.result='1',sr.actsignatory=signatory,
                   memo=p_Memo,signfilepath=v_SignFilePath,--Fun_Get_SignFilePath_Granter(signrecordid,p_UserID),
                   SIGNTYPE=p_SignType,grantee=p_UserID,ip=p_UserIP,mac=substr(p_UserMAC,0,17)
            WHERE SignrecordID=v_srid;
          --自己簽核或代理人簽核
          ELSE
            v_SignFilePath := Fun_Get_SignFilePath_con(p_applyID,p_UserID,p_SignFilePath);
            UPDATE WF_SignRecord sr SET sr.endtime=SYSDATE,sr.result='1',sr.actsignatory=p_UserID,
                   memo=p_Memo,signfilepath= v_SignFilePath,-- Fun_Get_SignFilePath(p_UserID,p_SignFilePath),
                   SIGNTYPE=p_SignType,ip=p_UserIP,mac=substr(p_UserMAC,0,17)
            WHERE SignrecordID=v_srid;
          END IF;
          SELECT prcstepid,signatory INTO v_prcstepid,v_cur_uid
          FROM WF_signrecord WHERE SignrecordID=v_srid;

          --SELECT signmode,result INTO v_signmode,v_result FROM SIGNRECORD WHERE signrecordid=p_SignRecordID;
          --檢查是否有會簽人員未簽核完成
          SELECT COUNT(*) INTO v_together_count FROM WF_signrecord s1,WF_signrecord s2
          WHERE s1.applyid=s2.applyid AND s1.prcstepid=s2.prcstepid AND s1.signrecordid=v_srid
                AND s2.result='A';

          --注記新的預簽核
          --檢查未簽核人員中是否有和當前簽核人員相同的簽核點？
          --若有，則需注記其為預簽核
          --若為代理人或授權人，暫不處理代理人和授權人的預簽核
          SELECT COUNT(*) INTO v_count1
            FROM WF_signrecord
           WHERE applyid=p_ApplyID AND RESULT IN('N')
                 AND mustsign='1' AND signatory=v_cur_uid AND signbefore='N';
          IF v_count1>0 THEN
             UPDATE WF_signrecord sr
                SET signbefore='Y'
              WHERE applyid=p_ApplyID AND RESULT IN('N')
                    AND mustsign='1' AND signatory=v_cur_uid AND signbefore='N';
          END IF;
          --處理預簽核事項：
          --   相同簽核人在同一單據里需簽核多次時，當他第一次簽核完成時，
          --   便將后面的簽核點設為預簽核，待該簽核點前面的簽核點簽核完成時，再將其自動簽核
          --若簽核模式不是會簽
          IF v_together_count=0 THEN
            --判斷此單據是否還有人員未簽核完成
            SELECT COUNT(*) INTO v_signfinish FROM WF_signrecord
            WHERE applyid=p_ApplyID AND result IN('A','N') AND substart='N' AND mustsign='1';
            IF v_signfinish > 0  THEN
              --更新當前單據狀態  及 激活下一簽核點的簽核人
              IF P_PRIORITY IS NULL OR P_PRIORITY='普通' OR P_PRIORITY=' ' OR length(rtrim(p_priority))>1 THEN
                 V_PRIORITY := '0';
              ELSE
                 V_PRIORITY := p_priority;
              END IF;
            
              --增加此判斷區分子流程和非子流程處理。
              SELECT COUNT(*) INTO v_count_substart FROM WF_signrecord SR
               WHERE applyid=p_ApplyID AND RESULT IN ('A','W','N')
                     AND SR.SUBSTART='Y';
              IF v_count_substart>0 THEN
                  UPDATE WF_signrecord SET starttime=SYSDATE,result='A'
                   WHERE applyid=p_ApplyID AND processid=v_processid AND signorder=(
                         SELECT min(signorder)
                           FROM WF_signrecord
                          WHERE applyid=p_applyid AND processid=v_processid AND
                                RESULT IN('A','N') AND mustsign='1'
                         ) AND result='N';
              ELSE
                  UPDATE WF_signrecord SET starttime=SYSDATE,result='A'
                   WHERE applyid=p_ApplyID AND signorder=(
                         SELECT min(signorder)
                           FROM WF_signrecord
                          WHERE applyid=p_applyid AND
                                RESULT IN('A','N') AND mustsign='1'
                         ) AND result='N';
              END IF;
              --檢查下一簽核點是否子流程，若是，則需啟動子流程第一簽核點
              SELECT substart,SIGNRECORDID
                INTO v_substart,V_SIGNRECORDID2
                FROM WF_signrecord
               WHERE applyid=p_ApplyID AND result='A' AND rownum=1;
              IF v_substart='Y' THEN--子流程簽核點，啟動子流程
                 UPDATE WF_signrecord
                    SET starttime=SYSDATE,result='A'
                  WHERE applyid=p_ApplyID AND paresignrecordId IN(
                        SELECT signrecordid FROM WF_signrecord
                         WHERE applyid=p_applyid AND RESULT='A' AND substart='Y'
                        ) AND signorder=1 AND RESULT='N';
                 --處理子流程中的預處理
              END IF;
              SELECT COUNT(*) INTO v_count1
                   FROM WF_signrecord sr
                  WHERE applyid=p_ApplyID;
              IF v_count1>0 THEN--增加此判斷,防止子流程直接簽核完成了
                v_cur_signers := FUN_GET_CUR_SIGNERS(P_APPLYID,'0');


                v_cursignersub := substr(v_cur_signers,1,instr(v_cur_signers,',')-1);
                v_cursignernamesub := substr(v_cur_signers,instr(v_cur_signers,'#')+1);
                v_cursignernamesub := substr(v_cursignernamesub,1,instr(v_cursignernamesub,',')-1);

                IF length(v_cur_signers) > 60 THEN
                   UPDATE WF_apply
                      SET currentstatus='300.002',--priority=V_PRIORITY,
                          cursigner=v_cursignersub,cursignernm=v_cursignernamesub,haserror='N'
                    WHERE applyid=p_ApplyId;
                ELSE
                    --UPDATE apply SET cursigner=substr(v_cur_signers,1,instr(v_cur_signers,'#')-1),
                    --       cursignernm=substr(v_cur_signers,instr(v_cur_signers,'#')+1) WHERE applyid=p_ApplyId;
                    UPDATE WF_apply
                       SET currentstatus='300.002',--priority=V_PRIORITY,
                           cursigner=substr(v_cur_signers,1,instr(v_cur_signers,'#')-1),
                           cursignernm=substr(v_cur_signers,instr(v_cur_signers,'#')+1),haserror='N'
                     WHERE applyid=p_ApplyId;

                END IF;
                IF v_currentstatus='300.001' THEN
                   UPDATE WF_apply SET priority=V_PRIORITY,haserror='N' WHERE applyid=p_ApplyID;
                END IF;
              END IF;
            ELSE
              SELECT COUNT(*) INTO v_count1 FROM WF_actioncontrol ac
               WHERE ac.applyid=p_ApplyID AND ac.actiontype='1';
              SELECT controls,fact_no INTO v_controls,v_fact_no
                FROM WF_apply WHERE applyid=p_ApplyID;
              IF v_controls IS NOT NULL AND substr(replace(v_controls,',',''),7,1)='1' AND
                 v_count1=0 THEN--有關鍵動作程式未設置 2010-6-29 fans
                 ROLLBACK;
                 P_EXECRESULT:='N';
                 --寫錯誤記錄檔
                 raise_application_error(-20000,'最后签核人未设置动作，不可以结案，请检查流程');
                
                 --COMMIT;
                 RETURN;
              END IF;

              --更新子流程啟動點，如果有.2010-01-05 by fans
              UPDATE WF_signrecord
                 SET actsignatory=signatory,endtime=Sysdate,RESULT='1'
               WHERE applyid=p_ApplyID AND substart='Y' AND RESULT='A';

              UPDATE WF_apply SET currentstatus='300.003',enddate=SYSDATE WHERE applyid=p_ApplyID;
              --將此單據所有的簽核記錄移至簽核歷史記錄檔，並將簽核歷史記錄檔中的是否最終簽核設為1
              v_cur_signers := FUN_GET_CUR_SIGNERS(P_APPLYID,'1');

              v_cursignersub := substr(v_cur_signers,1,instr(v_cur_signers,',')-1);
              v_cursignernamesub := substr(v_cur_signers,instr(v_cur_signers,'#')+1);
              v_cursignernamesub := substr(v_cursignernamesub,1,instr(v_cursignernamesub,',')-1);

              IF length(v_cur_signers) > 60 THEN
                 UPDATE WF_apply SET cursigner=v_cursignersub,
                        cursignernm=v_cursignernamesub WHERE applyid=p_ApplyId;
              ELSE
                  UPDATE WF_apply SET cursigner=substr(v_cur_signers,1,instr(v_cur_signers,'#')-1),
                         cursignernm=substr(v_cur_signers,instr(v_cur_signers,'#')+1) WHERE applyid=p_ApplyId;
              END IF;

              --PRO_ENGINE_MOVESIGNS_ALL(p_ApplyID);
              v_newstatus := '300.003';
            END IF;
          ELSE
              v_cur_signers := FUN_GET_CUR_SIGNERS(P_APPLYID,'0');
              v_cursignersub := substr(v_cur_signers,1,instr(v_cur_signers,',')-1);
              v_cursignernamesub := substr(v_cur_signers,instr(v_cur_signers,'#')+1);
              v_cursignernamesub := substr(v_cursignernamesub,1,instr(v_cursignernamesub,',')-1);

              IF length(v_cur_signers) > 60 THEN
                 UPDATE WF_apply SET cursigner=v_cursignersub,CURRENTSTATUS='300.002',
                        cursignernm=v_cursignernamesub,haserror='N' WHERE applyid=p_ApplyId;
              ELSE
                  UPDATE WF_apply SET cursigner=substr(v_cur_signers,1,instr(v_cur_signers,'#')-1),
                         cursignernm=substr(v_cur_signers,instr(v_cur_signers,'#')+1),
                         CURRENTSTATUS='300.002',haserror='N' WHERE applyid=p_ApplyId;
              END IF;
          END IF;
          
          SELECT currentstatus INTO v_newstatus FROM WF_apply WHERE applyid=p_applyid;
          --統計單據數目  PRO_ENGIN_OUT_INTERFACE()
          p_ExecResult := 'Y';

          --COMMIT;
          Exception
          When others THEN
               ROLLBACK;
               P_EXECRESULT:='N';
               --寫錯誤記錄檔
               v_error := SQLERRM;
               PRO_WRITE_LOGS_SMP(p_applyid,v_srid,trim(p_UserID),
                   'EWF系統簽核後台錯誤！==>PRO_ENGINE_SIGNAPPROVE,','Sign',
                   '【PRO_ENGINE_SIGNAPPROVE】'||p_applyid||','||v_srid||
                   ','||trim(p_UserID)||','||v_error, 'EWF');
                raise_application_error(-20000,v_error);    
               --COMMIT;

        END;
        END;
        ";
        #endregion

        #region PRO_ENGINE_SIGNBACK
        public const string PRO_ENGINE_SIGNBACK = @"
        -- Created on 2013/4/27 by ADMINISTRATOR 
        declare 
          -- Local variables here
            p_SignRecordID   WF_SIGNRECORD.SIGNRECORDID%TYPE:={0};--  當前簽核記錄ID
            p_ApplyID   WF_APPLY.APPLYID%TYPE:={1};--  當前單據ID
            P_PRCSTEPID  WF_SIGNRECORD.PRCSTEPID%TYPE:={2}; --節點ID
            p_UserID   WF_APPLY.APPLiER%TYPE:='{3}';--  當前用戶ID
            p_SignType   WF_SIGNRECORD.SIGNTYPE%TYPE:='{4}';--  當前用戶對單據的簽核類型，其值如下：1：自己簽核： 2：代理人簽核：3：授權人簽核：
            p_SignFilePath   WF_SIGNRECORD.SIGNFILEPATH%TYPE:='{5}';--  簽名檔
            P_PRIORITY       WF_APPLY.PRIORITY%TYPE:='{6}';--優先級別
            p_Memo   WF_SIGNRECORD.MEMO%TYPE:='{7}';--  備注
            p_UserIP   WF_SIGNRECORD.IP%TYPE:='{8}';--  用戶IP
            p_UserMAC   WF_SIGNRECORD.MAC%TYPE:='{9}';--  用戶MAC
            P_OTHERUSERID  WF_APPLY.APPLIER%TYPE:='{10}';--會簽、轉呈、覆核人員
            P_BACKSIGNSTEPID  WF_SIGNRECORD.PRCSTEPID%TYPE:='{11}';--退簽點 2009-12-24改為退簽點的SignRecordId
            P_STEPNAME        WF_SIGNRECORD.STEPNAME%TYPE:='{12}';--轉呈、覆核節點名稱
            p_ExecResult   VARCHAR2(50);--  傳出參數，執行成功返回Y，失敗或發生異常返回N
            p_Case varchar2(500);
            v_applyid WF_apply.applyid%type;
            v_SignType varchar2(40):='1';
            v_prcstepid WF_signrecord.prcstepid%type;
            --v_Back2User Signrecord.Signatory%type;
            v_NextUsers     varchar2(4000);--注：退簽時，發送Mail包括退簽人到被退人間所有的已簽核人員都應收到Mail.
            v_ApplyNo       WF_apply.apply_no%type;
            v_addMemo       WF_signrecord.memo%type := p_Memo || '(退回' || to_Char(sysdate,'yyyy/mm/dd') || ')';
            v_SignOrder     WF_signrecord.signorder%type;
            v_SignOrder2     WF_signrecord.signorder%type;
            v_SQL            varchar2(4000);
            V_CURRENTSTATUS  WF_APPLY.CURRENTSTATUS%TYPE;
            V_PROCESSID      WF_APPLY.PROCESSID%TYPE;
            v_GranterSignpath  VARCHAR2(100);
            v_secright VARCHAR2(1);
            v_signatory WF_signrecord.signatory%TYPE;
            v_cur_uid   WF_signrecord.signatory%TYPE;
            v_control   WF_apply.controls%TYPE;
            v_paresignrecordid   WF_signrecord.paresignrecordid%TYPE;
            v_sub_processid      WF_signrecord.processid%TYPE;
            v_back_processid WF_signrecord.processid%TYPE;
            v_back_paresrid  WF_signrecord.paresignrecordid%TYPE;
            v_back_substart  WF_signrecord.substart%TYPE;
            v_signrecordid   WF_signrecord.signrecordid%TYPE;
        begin
          -- Test statements here

          
        BEGIN
          v_secright := fun_check_op_rights(p_ApplyID,p_SignRecordid,p_UserID);
          IF v_secright='0' THEN
             p_ExecResult := 'N';
             raise_application_error(-20000,'簽核發生錯誤:您沒有權限執行此動作,請刷新簽核列表后重試!');
             
             COMMIT;
             RETURN;
          END IF;
          IF p_SignRecordID IS NULL OR TRIM(p_SignRecordID) IS NULL THEN
             v_signrecordid:=fun_get_real_srid(p_ApplyID,p_UserID);
           ELSE
             v_signrecordid:=p_SignRecordID;
          END IF;

           Select ApplyID,prcstepid,signmode,signorder,signatory,paresignrecordid,processid
           into v_ApplyID,v_prcstepid,v_SignType,v_SignOrder,v_cur_uid,v_paresignrecordid,v_sub_processid
           from WF_signrecord where SignrecordID = v_signrecordid;

           select apply_no,CURRENTSTATUS,PROCESSID,fun_get_control_byindex(controls,6)
           into v_ApplyNo,V_CURRENTSTATUS,V_PROCESSID,v_control
           from WF_apply where applyid=v_applyid;

           --取得被退回簽核點資訊
           select signorder,s.signatory,s.processid,s.paresignrecordid,s.substart
             into v_SignOrder2,v_signatory,v_back_processid,v_back_paresrid,v_back_substart
             from WF_signrecord s
            where applyid=v_applyid and SignRecordId=p_BackSignStepID AND rownum=1;

           IF v_control='1' THEN
             p_ExecResult := 'N';
             raise_application_error(-20000,'当前类型单据被设置为不可以退签，请联络管理员');
             
             COMMIT;
             RETURN;
           END IF;
           --子流程不允許退回到主流程2009-12-24 fans
           IF v_sub_processid<>v_back_processid AND v_paresignrecordid<>-1 THEN
             p_ExecResult := 'N';
              raise_application_error(-20000,'操作錯誤:子流程不允許退簽到主流程，若有需要，請聯絡主流程簽核人員取回!');
             
             COMMIT;
             RETURN;
           END IF;
           --主流程不允許退回到子流程啟動點 2009-12-24 fans
           IF v_back_substart='Y' THEN
             p_ExecResult := 'N';
              raise_application_error(-20000,'操作錯誤:不允許退簽到子流程啟動點!');
             
             --COMMIT;
             RETURN;
           END IF;
          --更新簽核人本人的簽核結果! 2009-12-24 by fans
          -- AND processid=v_sub_processid 用于加入對子流程的控制
          -- 若為主流程，則v_sub_processid=v_processid,
          -- 若為子流程，則v_sub_processid控制只處理子流程退簽，不可以退回到主流程
          if p_SignType='3' then--授權的退簽
             SELECT Fun_Get_SignFilePath_Granter(v_signrecordid,p_UserID)
               INTO v_GranterSignpath
               FROM WF_signrecord  where SignrecordID=v_signrecordid;
              update WF_Signrecord d Set d.endtime=sysdate,d.actsignatory=signatory,
                     d.result='2',d.memo=memo||v_addMemo,d.signtype=p_SignType,d.grantee=p_UserID,
                     d.SignFilePath=v_GranterSignpath,ip=p_UserIP,mac=substr(p_UserMAC,0,17),username = FUN_GET_NODEUSERS(p_UserID)
               where applyid=v_applyid AND processid=v_sub_processid and RESULT = 'A';
          else
              v_GranterSignpath := Fun_Get_SignFilePath_con(p_applyID,p_UserID,p_SignFilePath);
              update WF_Signrecord d Set d.endtime=sysdate,d.actsignatory=p_UserID,
                     d.result='2',d.memo=memo||v_addMemo,d.signtype=p_SignType,
                     d.SignFilePath=v_GranterSignpath,
                     ip=p_UserIP,mac=substr(p_UserMAC,0,17),username = FUN_GET_NODEUSERS(p_UserID)
               where applyid=v_applyid AND processid=v_sub_processid and RESULT = 'A';
           end if;


          --插入被退回人員的簽核資料
          /*
           1. 子流程只能退回到對應的子流程，不能退到主流程
           2. 主流程可以跨子流程退簽，但不能直接退回到子流程啟動點
          */
          --插入主流程，如果為子流程，則直接為插入子流程
          Insert into WF_Signrecord(Fact_no,ProcessID,PrcstepID,applyID,signatory,Starttime,
                 IsIncrement,isRedirect,Back_mk,Stepname,Signorder,Signmode,sguid,Result,
                 Username,Formruleid,Modisigner,Ndisplay,Mustsign,Ylevel,Paresignrecordid,
                 Signbefore,Substart,Userconmids,Readmk,Wf_Code,Nodetype,Remoteid,Aud_Code,
                 Actsigner_Positnm,Step_Action)
          select Fact_no,Processid,prcStepID,applyid,
                 signatory, decode(signOrder,v_SignOrder2,sysdate,'') as starttime,
                 'N' as isincrement,'N' as isredirect,
                 decode(SignOrder,v_SignOrder2,'Y','N') as Back_mk,Stepname,Signorder,
                 Signmode,rowid,decode(SignOrder,v_SignOrder2,'A','N') as Result,
                 username,Formruleid,Modisigner,Ndisplay,Mustsign,Ylevel,Paresignrecordid,
                 Signbefore,Substart,Userconmids,Readmk,Wf_Code,Nodetype,Remoteid,Aud_Code,
                 Actsigner_Positnm,Step_Action
            from WF_signrecord
           where applyid=v_applyid and signorder >=v_SignOrder2 and processid=v_back_processid AND
                 signorder<=v_SignOrder;-- and ISiNCREMENT='N' AND ISREDIRECT='N';
          --如果是主流程，檢查是否有包含子流程啟動點，若有，則需將對應的子流程插入
          Insert into WF_Signrecord(Fact_no,ProcessID,PrcstepID,applyID,signatory,Starttime,
                 IsIncrement,isRedirect,Back_mk,Stepname,Signorder,Signmode,sguid,Result,
                 Username,Formruleid,Modisigner,Ndisplay,Mustsign,Ylevel,Paresignrecordid,
                 Signbefore,Substart,Userconmids,Readmk,Wf_Code,Nodetype,Remoteid,Aud_Code,
                 Actsigner_Positnm,Step_Action)
          select Fact_no,Processid,prcStepID,applyid,
                 signatory, '' as starttime,
                 'N' as isincrement,'N' as isredirect,'N' as Back_mk,Stepname,Signorder,
                 Signmode,rowid,'N' as Result,
                 username,Formruleid,Modisigner,Ndisplay,Mustsign,Ylevel,(
                 SELECT s1.signrecordid
                   FROM WF_signrecord s1,WF_signrecord s2
                  WHERE s2.applyid=sr.applyid AND s2.signrecordid=sr.paresignrecordid AND
                        s1.applyid=s2.applyid AND s1.processid=s2.processid AND
                        s1.signorder=s2.signorder AND s1.endtime IS NULL
                 ) AS paresignrecordId,
                 Signbefore,Substart,Userconmids,Readmk,Wf_Code,Nodetype,Remoteid,Aud_Code,
                 Actsigner_Positnm,Step_Action
            from WF_signrecord sr
           where applyid=v_applyid and paresignrecordid IN(
                   SELECT signrecordid
                     FROM WF_signrecord
                    where applyid=v_applyid and signorder >=v_SignOrder2 and
                          processid=v_back_processid AND signorder<=v_SignOrder and
                          substart='Y'
                 );

          Update WF_Apply
             Set Currentstatus='300.004',CURSIGNER =v_signatory,
                 CURSIGNERNM =FUN_GET_NODEUSERS(v_signatory),haserror='N'
           where applyid=v_ApplyID;

          --搬移舊的資料到SignRecord_His表裡
          --PRO_ENGINE_MOVESIGNS_Part(v_applyid,v_back_processid,v_SignOrder2,v_SignOrder);
          P_EXECRESULT:='Y';
          --計數
          --PRO_ENGIN_OUT_INTERFACE(V_APPLYID,v_processid,v_cur_uid,V_CURRENTSTATUS,'300.004','');
          --commit;

          Exception
          When Others then
               Rollback;
               P_EXECRESULT:='N';
               p_Case:=sqlerrm; 
                raise_application_error(-20000,p_Case);
             PRO_WRITE_LOGS_SMP(p_applyid,v_signrecordid,trim(p_UserID),
                   'EWF系統退簽後台錯誤'||p_Case,'Sign',
                   '【PRO_ENGINE_SIGNBACK】'||p_applyid||','||v_signrecordid||','||
                   P_BACKSIGNSTEPID||','||v_signrecordid||','||p_SignType||
                   ','||trim(p_UserID), 'EWF');
             --COMMIT;
        end;
        end;
";

        #endregion

        #region PRO_ENGINE_SIGNGETBACK
        public const string PRO_ENGINE_SIGNGETBACK = @"
            -- Created on 2013/4/27 by ADMINISTRATOR 
            declare 
              -- Local variables here
              i integer;
              p_SignRecordID   WF_SIGNRECORD.SIGNRECORDID%TYPE:={0};--  當前簽核記錄ID
              p_ApplyID   WF_APPLY.APPLYID%TYPE:={1};--  當前單據ID
              P_PRCSTEPID  WF_SIGNRECORD.PRCSTEPID%TYPE:={2}; --節點ID
              p_UserID   WF_APPLY.APPLiER%TYPE:={3};--  當前用戶ID
              p_SignType   WF_SIGNRECORD.SIGNTYPE%TYPE:='{4}';--  當前用戶對單據的簽核類型，其值如下：1：自己簽核： 2：代理人簽核：3：授權人簽核：
              p_SignFilePath   WF_SIGNRECORD.SIGNFILEPATH%TYPE:='{5}';--  簽名檔
              P_PRIORITY       WF_APPLY.PRIORITY%TYPE:='{6}';--優先級別
              p_Memo   WF_SIGNRECORD.MEMO%TYPE:='{7}';--  備注
              p_UserIP   WF_SIGNRECORD.IP%TYPE:='{8}';--  用戶IP
              p_UserMAC   WF_SIGNRECORD.MAC%TYPE:='{9}';--  用戶MAC
              P_OTHERUSERID  WF_APPLY.APPLIER%TYPE:='{10}';--會簽、轉呈、覆核人員
              P_BACKSIGNSTEPID  WF_SIGNRECORD.PRCSTEPID%TYPE:='{11}';--退簽點
              P_STEPNAME        WF_SIGNRECORD.STEPNAME%TYPE:='{12}';--轉呈、覆核節點名稱
              p_ExecResult   VARCHAR2(50);--  傳出參數，執行成功返回Y，失敗或發生異常返回N
              p_ExecCase      varchar2(5000);   
              -- Test statements here
              v_NextUsers    varchar2(4000);
              v_MaxStepID    WF_apply.applyid%type;
              v_MinStepID    WF_apply.applyid%type;
              v_ApplyNO      WF_apply.apply_no%type;
              v_addMemo       WF_signrecord.memo%type := p_Memo || '(取回' || to_Char(sysdate,'yyyy/mm/dd') || ')';
              v_count           number(5);
              v_SignOrder     WF_signrecord.signorder%type;
              v_SignOrder2    WF_signrecord.signorder%type := 1;
              v_SignMode      WF_signrecord.signmode%type;
              V_CURRENTSTATUS WF_APPLY.CURRENTSTATUS%TYPE;
              V_PROCESSID     WF_APPLY.PROCESSID%TYPE;
              v_secright VARCHAR2(1);
              v_cur_signers       VARCHAR2(222);--當前簽核人id,id,id#name,name,name
              v_cur_uid           WF_signrecord.signatory%TYPE;
              V_PROCESSCODE       WF_APPLY.PROCESSCODE%TYPE;
              V_CONTROL           WF_APPLY.CONTROLS%TYPE;
              V_PARESIGNRECORDID  WF_SIGNRECORD.PARESIGNRECORDID%TYPE;
              V_SUB_PROCESSID     WF_SIGNRECORD.PROCESSID%TYPE;
              V_ISSUB             BOOLEAN;
              V_BACKED_SRID       NUMBER;--取回的簽核點ID
              V_BACK_SRID         NUMBER;--被取回簽核點ID
              V_SUBSTART          WF_SIGNRECORD.SUBSTART%TYPE;
              V_HASLOCK           NUMBER;--單據是否已被當前簽核人員設置為待處理，如果是，則不可以取回
              V_CURSIGNERNM       WF_APPLY.CURSIGNERNM%TYPE;--LOCK單據的待審核人員
            begin
             


              SELECT COUNT(*) INTO v_count FROM vie_applycur WHERE applyid = p_ApplyID;
              IF v_count >0 THEN
                select apply_no,CURRENTSTATUS,PROCESSID,PROCESSCODE,fun_get_control_byindex(CONTROLS,6)
                  INTO v_ApplyNO,V_CURRENTSTATUS,V_PROCESSID,V_PROCESSCODE,V_CONTROL
                  from vie_applycur where applyid=p_ApplyID;
              
              END IF;
             /* IF V_CURRENTSTATUS = '300.003' THEN
              pro_engine_signgetback_filish(p_SignRecordID,p_ApplyID,P_PRCSTEPID,p_UserID,
                  p_SignType,p_SignFilePath,P_PRIORITY,p_Memo,p_UserIP,p_UserMAC,
                  P_OTHERUSERID,P_BACKSIGNSTEPID,P_STEPNAME,p_ExecResult);
                  RETURN ;
               END IF;
            */
               IF v_control='1' THEN
                 ROLLBACK;
                 p_ExecResult := 'N';
                  raise_application_error(-20000,'操作錯誤:此單據已經不允許再被取回!');
                 
                 --COMMIT;
                 RETURN;
               END IF;

               --檢查單據是否已被Lock（待處理） 2010-01-04
               SELECT COUNT(*) INTO V_HASLOCK
                 FROM WF_signrecord
                WHERE applyid=p_ApplyID AND result='W';
               IF V_HASLOCK>0 THEN
                 ROLLBACK;
                 p_ExecResult := 'N';
                 SELECT username INTO V_CURSIGNERNM
                   FROM WF_signrecord
                  WHERE applyid=p_ApplyID AND result='W' AND rownum=1;
                   raise_application_error(-20000,'操作錯誤:此單據已經被['||V_CURSIGNERNM||']設為待處理，您無法直接取回！'||
                       '請聯絡['||V_CURSIGNERNM||']處理！');
                
                 --COMMIT;
                 RETURN;
               END IF;

              /*以下取得取回人在簽核流程中的位置,
                 1. 子流程不可以從主流程中取回
                 2. 若當前簽核點是子流程，則處理取回時，優先考慮主流程中取回
              */

              SELECT COUNT(*) INTO V_COUNT
                FROM WF_SIGNRECORD SR
               WHERE APPLYID=p_ApplyID AND RESULT='A' AND SR.SUBSTART='Y';
              IF V_COUNT>0 THEN--從子流程中取回
                 V_ISSUB:=TRUE;
              ELSE
                 V_ISSUB:=FALSE;
              END IF;
              --判斷是否主流程中的當事人取回
              select count(*) into v_count
                from WF_signrecord
               where applyid=p_ApplyID and Result IN('A','1','4') AND paresignrecordID=-1 AND
                     (signatory=rtrim(p_UserID) or actsignatory=rtrim(p_UserID));
              if v_count>0 then--查詢是否當事人
                 V_ISSUB:=FALSE;
                 select distinct signorder,signmode,prcstepid,processid,
                        SIGNRECORDID,SUBSTART
                   into v_SignOrder2,v_SignMode,v_MinStepID,V_SUB_PROCESSID,
                        V_BACKED_SRID,V_SUBSTART
                   from WF_signrecord
                  where applyid=p_ApplyID and Result IN('A','1','4') AND paresignrecordID=-1 AND
                        (signatory=rtrim(p_UserID) or actsignatory=rtrim(p_UserID)) AND rownum=1;
              else--判斷是否主流程中的代理人取回
                  --V_ISSUB:=FALSE;
                  select COUNT(*)
                    into v_count
                    from WF_signrecord vs,
                         (select * from vie_authorizes where rtrim(grantee)=rtrim(p_UserID)) aa
                   where (rtrim(aa.granter)=vs.signatory or rtrim(aa.granter)=vs.actsignatory)
                         and vs.APPLYID=p_applyID and Result IN('A','1','4') and
                         paresignrecordID=-1 AND rownum=1;
                  IF v_count>0 THEN
                    V_ISSUB:=FALSE;--主流程中取回
                    select distinct signorder,signmode,prcstepid,VS.processid,
                           SIGNRECORDID,SUBSTART
                      into v_SignOrder2,v_SignMode,v_MinStepID,V_SUB_PROCESSID,
                           V_BACKED_SRID,V_SUBSTART
                      from WF_signrecord vs,
                           (select * from vie_authorizes where rtrim(grantee)=rtrim(p_UserID)) aa
                     where (rtrim(aa.granter)=vs.signatory or rtrim(aa.granter)=vs.actsignatory)
                           and vs.APPLYID=p_applyID and Result IN('A','1','4') and
                           paresignrecordID=-1 AND rownum=1;
                  ELSIF V_ISSUB THEN--子流程內部取回
                  --若并行的子流程中，已簽核完成的子流程中的人員取回：允許！
                    SELECT COUNT(*) INTO v_count
                    FROM (
                      select signrecordid
                          from WF_signrecord
                         where applyid=p_ApplyID and Result IN('A','1','4') AND paresignrecordID<>-1 AND
                               (signatory=rtrim(p_UserID) or actsignatory=rtrim(p_UserID))
                      UNION
                      select vs.signrecordid
                        FROM WF_SIGNRECORD VS,
                             (select * from vie_authorizes where rtrim(grantee)=rtrim(p_UserID)) aa
                       where (rtrim(aa.granter)=vs.signatory or rtrim(aa.granter)=vs.actsignatory)
                             and vs.APPLYID=p_applyID and Result IN('A','1','4') and
                             paresignrecordID<>-1
                    );
                    IF v_count=0 THEN--無法獲取取回的位置
                       ROLLBACK;
                       p_ExecResult := 'N';
                        raise_application_error(-20000,'系統錯誤:無法正常獲取当前操作人資訊，請聯絡系統管理員!');
                       
                       RETURN;
                    END IF;
                    --取得取回的子流程簽核點資訊
                    V_ISSUB:=TRUE;
                    select signorder,signmode,prcstepid,processid,SIGNRECORDID,SUBSTART
                      into v_SignOrder2,v_SignMode,v_MinStepID,V_SUB_PROCESSID,
                           V_BACKED_SRID,V_SUBSTART
                      from (
                        select *
                            from WF_signrecord
                           where applyid=p_ApplyID and Result IN('A','1','4') AND paresignrecordID<>-1 AND
                                 (signatory=rtrim(p_UserID) or actsignatory=rtrim(p_UserID))
                        UNION
                        select vs.*
                          from WF_signrecord vs,
                               (select * from vie_authorizes where rtrim(grantee)=rtrim(p_UserID)) aa
                         where (rtrim(aa.granter)=vs.signatory or rtrim(aa.granter)=vs.actsignatory)
                               and vs.APPLYID=p_applyID and Result IN('A','1','4') and
                               paresignrecordID<>-1
                      )
                     where rownum=1;
                  ELSE--在主流程中沒有找到取回人，可能是子流程中的人員從主流程取回，不允許！
                     ROLLBACK;
                     p_ExecResult := 'N';
                     select count(*) into v_count
                       from WF_signrecord
                      where applyid=p_ApplyID and Result IN('A','W') AND paresignrecordID<>-1;
                     IF v_count>0 THEN--子流程取回
                      raise_application_error(-20000,'操作錯誤:子流程中不允許取回!');
                       
                     ELSE
                      raise_application_error(-20000,'系統錯誤:無法正常獲取当前操作人資訊，請聯絡系統管理員!');
                      
                     END IF;
                     COMMIT;
                     RETURN;
                  END IF;
              end if;
              IF V_ISSUB THEN--獲取被取回的子流程簽核點
                --是否子流程最后簽核點自己取回
                SELECT COUNT(*) INTO v_count
                  FROM WF_signrecord sr
                 WHERE applyid=p_ApplyID AND sr.paresignrecordid<>-1 AND
                       sr.processid=V_SUB_PROCESSID AND RESULT IN('A','W');
                IF v_count>0 THEN
                  select distinct signorder,signatory,signrecordid,substart
                    into v_SignOrder,v_cur_uid,V_BACK_SRID,V_SUBSTART
                    from WF_signrecord
                   where applyid=p_ApplyID and RESULT IN('A','W') AND
                         processid=V_SUB_PROCESSID AND rownum =1;
                ELSE--當前子流程已簽核完成，取得最后簽核點
                  select distinct signorder,signatory,signrecordid,substart
                    into v_SignOrder,v_cur_uid,V_BACK_SRID,V_SUBSTART
                    from WF_signrecord
                   where applyid=p_ApplyID and RESULT IN('1') AND
                         processid=V_SUB_PROCESSID AND signorder=(
                            SELECT MAX(signorder) FROM WF_signrecord
                             WHERE applyid=p_applyid AND processid=V_SUB_PROCESSID
                                   AND RESULT='1'
                         ) AND rownum =1;
                  --插入一筆最后簽核點的替換點
                  /*insert into Signrecord
                  select *
                    from signrecord
                   where applyid=p_ApplyID and signrecordid=V_BACK_SRID;*/
                END IF;
              ELSE
                select distinct signorder,signatory,signrecordid,substart
                  into v_SignOrder,v_cur_uid,V_BACK_SRID,V_SUBSTART
                  from WF_signrecord
                 where applyid=p_ApplyID AND paresignrecordid=-1 and
                       RESULT IN('A','W') AND rownum =1;
              END IF;



              --更新被取回簽核點
              
              --2012-6-1 Hou 處理會簽簽核點取回時發生簽核記錄異常的問題
              IF V_ISSUB THEN--子流程只需處理對應的子流程 2012-8-7 Hou

                --檢查單據是否跨區簽核中,若是,則控制不允許取回--2012-12-5 Hou
                SELECT COUNT(DISTINCT sr.wf_code) INTO v_count FROM WF_signrecord sr
                 WHERE sr.applyid=p_ApplyID AND processid=(SELECT processid FROM Wf_signrecord WHERE signrecordid=V_BACK_SRID) AND
                       (RESULT IN('A','W') OR sr.signrecordid=V_BACK_SRID);
                IF v_count>1 THEN--操作屬於不同的區域,禁止取回
                   raise_application_error(-20000,'此單據跨區簽核中,不能取回!請聯絡簽核中的主管退件!');
                END IF;

                update WF_Signrecord
                   set endtime=sysdate,Result='3',memo=memo||v_addMemo,
                       actSignatory=p_UserID,ip=p_UserIP,mac=substr(p_UserMAC,0,17),
                       username = decode(signatory,'-1',username,FUN_GET_NODEUSERS(p_UserID))--FUN_GET_NODEUSERS(p_UserID)
                 where applyid=p_ApplyID AND RESULT IN('A','W') AND
                       processid=(SELECT processid FROM Wf_signrecord WHERE signrecordid=V_BACK_SRID);--子流程也適用
              ELSE--主流程需處理所有待審核狀態的簽核點
                --檢查單據是否跨區簽核中,若是,則控制不允許取回--2012-12-5 Hou
                SELECT COUNT(DISTINCT sr.wf_code) INTO v_count FROM WF_signrecord sr
                 WHERE sr.applyid=p_ApplyID AND sr.paresignrecordid=-1 AND
                       (RESULT IN('A','W') OR sr.signrecordid=V_BACK_SRID);
                IF v_count>1 THEN--操作屬於不同的區域,禁止取回
                   raise_application_error(-20000,'此單據跨區簽核中,不能取回!請聯絡簽核中的主管退件!');
                END IF;

                update WF_Signrecord
                   set endtime=sysdate,Result='3',memo=memo||v_addMemo,
                       actSignatory=p_UserID,ip=p_UserIP,mac=substr(p_UserMAC,0,17),
                       username = decode(signatory,'-1',username,FUN_GET_NODEUSERS(p_UserID))--跨區的用戶姓名不能替換
                 where applyid=p_ApplyID AND RESULT IN('A','W');-- AND
                       --processid=(SELECT processid FROM signrecord WHERE signrecordid=V_BACK_SRID);--子流程也適用
              END IF;

              --插入被取回人員的簽核記錄
              IF V_ISSUB THEN
                insert into WF_Signrecord(Fact_no,processid,prcstepid,applyid,signatory,starttime,
                       isincrement,isRedirect,Back_MK,Stepname,Signorder,Signmode,sguid,Result,
                       Username,formruleid,Modisigner,Ndisplay,Mustsign,Ylevel,Paresignrecordid,
                       Signbefore,Substart,Userconmids,Readmk,Wf_Code,Nodetype,Remoteid,Aud_Code,
                     Actsigner_Positnm,Step_Action)
                select fact_no,processid,prcstepid,applyid,signatory,
                       decode(signorder,v_SignOrder2,sysdate,'') as starttime,isincrement,isRedirect,
                       decode(signorder,v_SignOrder2,'Y','N') as Back_MK,Stepname,Signorder,Signmode,rowid,
                       decode(signorder,v_SignOrder2,'A','N') as Result,
                       --decode(signorder,v_SignOrder2,FUN_GET_NODEUSERS(p_UserID) ,username) as username,
                       decode(signatory,'-1',username,FUN_GET_NODEUSERS(signatory)) as username,Formruleid,Modisigner,Ndisplay,
                       Mustsign,Ylevel,Paresignrecordid,Signbefore,Substart,Userconmids,Readmk,Wf_Code,Nodetype,Remoteid,Aud_Code,
                     Actsigner_Positnm,Step_Action
                  from WF_signrecord
                 where applyid=p_ApplyID AND processid=V_SUB_PROCESSID and
                       signorder between v_SignOrder2 and v_SignOrder and
                       endtime is not null;
               ELSE
                 --新增主流程
                insert into WF_Signrecord(Fact_no,processid,prcstepid,applyid,signatory,starttime,
                       isincrement,isRedirect,Back_MK,Stepname,Signorder,Signmode,sguid,Result,
                       Username,formruleid,Modisigner,Ndisplay,Mustsign,Ylevel,Paresignrecordid,
                       Signbefore,Substart,Userconmids,Readmk,Wf_Code,Nodetype,Remoteid,Aud_Code,
                     Actsigner_Positnm,Step_Action)
                select fact_no,processid,prcstepid,applyid,signatory,
                       decode(signorder,v_SignOrder2,sysdate,'') as starttime,isincrement,isRedirect,
                       decode(signorder,v_SignOrder2,'Y','N') as Back_MK,Stepname,Signorder,Signmode,rowid,
                       decode(signorder,v_SignOrder2,'A','N') as Result,
                       --decode(signorder,v_SignOrder2,FUN_GET_NODEUSERS(p_UserID) ,username) as username,
                       decode(signatory,'-1',username,FUN_GET_NODEUSERS(signatory)) as username,Formruleid,Modisigner,Ndisplay,
                       Mustsign,Ylevel,Paresignrecordid,Signbefore,Substart,Userconmids,Readmk,Wf_Code,Nodetype,Remoteid,Aud_Code,
                     Actsigner_Positnm,Step_Action
                  from WF_signrecord
                 where applyid=p_ApplyID AND paresignrecordid=-1 and
                       signorder between v_SignOrder2 and v_SignOrder and
                       endtime is not null;
                 --新增子流程
                insert into WF_Signrecord(Fact_no,processid,prcstepid,applyid,signatory,starttime,
                       isincrement,isRedirect,Back_MK,Stepname,Signorder,Signmode,sguid,Result,
                       Username,formruleid,Modisigner,Ndisplay,Mustsign,Ylevel,Paresignrecordid,
                       Signbefore,Substart,Userconmids,Readmk,Wf_Code,Nodetype,Remoteid,Aud_Code,
                     Actsigner_Positnm,Step_Action)
                select fact_no,processid,prcstepid,applyid,signatory,
                       '' as starttime,isincrement,isRedirect,
                       'N' as Back_MK,Stepname,Signorder,Signmode,rowid,
                       'N' as Result,
                       --decode(signorder,v_SignOrder2,FUN_GET_NODEUSERS(p_UserID) ,username) as username,
                       decode(signatory,'-1',username,FUN_GET_NODEUSERS(signatory)) as username,Formruleid,Modisigner,
                       Ndisplay,Mustsign,Ylevel,(
                     SELECT s1.signrecordid
                       FROM Wf_signrecord s1,Wf_signrecord s2
                      WHERE s2.applyid=sr.applyid AND s2.signrecordid=sr.paresignrecordid AND
                            s1.applyid=s2.applyid AND s1.processid=s2.processid AND
                            s1.signorder=s2.signorder AND s1.endtime IS NULL
                     ) AS paresignrecordId,Signbefore,Substart,Userconmids,Readmk,Wf_Code,Nodetype,Remoteid,Aud_Code,
                     Actsigner_Positnm,Step_Action
                  from WF_signrecord sr
                 where applyid=p_ApplyID AND paresignrecordid IN(
                      SELECT signrecordid
                        from WF_signrecord
                       where applyid=p_ApplyID AND paresignrecordid=-1 and
                             signorder between v_SignOrder2 and v_SignOrder and
                             endtime is not NULL AND substart='Y'
                       );

               END IF;

            --    select fun_get1_signed_users(p_ApplyID,v_SignOrder2,v_SignOrder) into v_NextUsers from dual;
            --   pro_engine_sendmail(v_ApplyNO,p_UserID,v_NextUsers,'3');

              /*IF V_ISSUB THEN
                 PRO_ENGINE_MOVESIGNS_Part(p_ApplyID,V_SUB_PROCESSID,v_SignOrder2,v_SignOrder);
              ELSE
                 PRO_ENGINE_MOVESIGNS_Part(p_ApplyID,V_PROCESSID,v_SignOrder2,v_SignOrder);
              END IF;
            */
              v_cur_signers := FUN_GET_CUR_SIGNERS(P_APPLYID,'0');
              update Wf_apply set Wf_apply.currentstatus='300.006',haserror='N'
              ,CURSIGNER =substr(v_cur_signers,1,instr(v_cur_signers,'#')-1)
              ,CURSIGNERNM =substr(v_cur_signers,instr(v_cur_signers,'#')+1)
               where applyid=p_ApplyID;
              P_EXECRESULT:='Y';
              --計數


              Exception
              when others then
                 Rollback;
                 p_ExecResult := 'N';
                 p_ExecCase:=sqlerrm;
                
                 PRO_WRITE_LOGS_SMP(p_applyid,p_SignRecordID,trim(p_UserID),
                       'EWF系統取回程式錯誤:'||replace(p_ExecCase,'ORA-20000: ',''),'Sign',
                       '【PRO_ENGINE_SIGNGETBACK】'||p_applyid||','||p_SignRecordID||
                       ','||trim(p_UserID), 'EWF');
                  raise_application_error(-20000,p_ExecCase);

                 RETURN;



            end;";
        #endregion

        #region PRO_ENGINE_SIGNGETBACK_FILISH
        public const string PRO_ENGINE_SIGNGETBACK_FILISH = @"
            -- Created on 2013/4/27 by ADMINISTRATOR 
            declare 
              -- Local variables here
              i integer;
              p_SignRecordID   WF_SIGNRECORD.SIGNRECORDID%TYPE:={0};--  當前簽核記錄ID
              p_ApplyID   WF_APPLY.APPLYID%TYPE:={1};--  當前單據ID
              P_PRCSTEPID  WF_SIGNRECORD.PRCSTEPID%TYPE:={2}; --節點ID
              p_UserID   WF_APPLY.APPLiER%TYPE:={3};--  當前用戶ID
              p_SignType   WF_SIGNRECORD.SIGNTYPE%TYPE:='{4}';--  當前用戶對單據的簽核類型，其值如下：1：自己簽核： 2：代理人簽核：3：授權人簽核：
              p_SignFilePath   WF_SIGNRECORD.SIGNFILEPATH%TYPE:='{5}';--  簽名檔
              P_PRIORITY       WF_APPLY.PRIORITY%TYPE:='{6}';--優先級別
              p_Memo   WF_SIGNRECORD.MEMO%TYPE:='{7}';--  備注
              p_UserIP   WF_SIGNRECORD.IP%TYPE:='{8}';--  用戶IP
              p_UserMAC   WF_SIGNRECORD.MAC%TYPE:='{9}';--  用戶MAC
              P_OTHERUSERID  WF_APPLY.APPLIER%TYPE:='{10}';--會簽、轉呈、覆核人員
              P_BACKSIGNSTEPID  WF_SIGNRECORD.PRCSTEPID%TYPE:='{11}';--退簽點
              P_STEPNAME        WF_SIGNRECORD.STEPNAME%TYPE:='{12}';--轉呈、覆核節點名稱
              p_ExecResult   VARCHAR2(50);--  傳出參數，執行成功返回Y，失敗或發生異常返回N
              p_ExecCase  varchar2(5000);
              v_NextUsers    varchar2(4000);
              v_MaxStepID    WF_apply.applyid%type;
              v_MinStepID    WF_apply.applyid%type;
              v_ApplyNO      WF_apply.apply_no%type;
              v_addMemo       WF_signrecord.memo%type := p_Memo || '(結案後取回' || to_Char(sysdate,'yyyy/mm/dd') || ')';
              v_count           number(5);
              v_count2          NUMBER(5);
              v_SignOrder     WF_signrecord.signorder%type;
              v_SignMode      WF_signrecord.signmode%type;
              V_CURRENTSTATUS WF_APPLY.CURRENTSTATUS%TYPE;
              V_PROCESSID     WF_APPLY.PROCESSID%TYPE;
              v_count1 NUMBER:=0;
              v_statttime WF_signrecord.starttime%TYPE;
              v_tempapplyid WF_apply.applyid%TYPE;
              v_cancancel   WF_apply.cancancel%TYPE;
              v_cur_uid  WF_signrecord.signatory%TYPE;
              v_signrecordid NUMBER;
              v_contenttb WF_forms.contenttb%TYPE;
              v_substart  WF_signrecord.substart%TYPE;
              v_paresignrecordid WF_signrecord.paresignrecordid%TYPE;
              v_sub_processid    WF_signrecord.processid%TYPE;
            begin
              -- Test statements here

            BEGIN
              --30507278,31072052,1,181243,1,,,tttt,172.18.17.141,,,,
              SELECT COUNT(*) INTO v_count FROM WF_apply WHERE applyid = p_ApplyID;
              IF v_count >0 THEN
                 --取得被取回的簽核點資訊
                 --若為子流程簽核中,則取得子流程啟動點的資訊
                 SELECT signrecordid,signorder,processid,paresignrecordid,substart
                   INTO v_signrecordid,v_SignOrder,v_sub_processid,v_paresignrecordid,v_substart
                   FROM WF_signrecord_his
                 WHERE applyid=p_applyID AND signorder=(
                        select max(signorder) from WF_signrecord_his
                         where applyid=p_ApplyID AND isrealsign='1' AND MUSTSIGN='1' AND paresignrecordid=-1
                        ) AND isrealsign='1' AND paresignrecordid=-1 AND rownum=1;
                 SELECT ap.cancancel,f.contenttb
                   INTO v_cancancel,v_contenttb
                   FROM WF_apply ap,WF_forms f
                  WHERE ap.processcode=f.processcode AND ap.applyid=p_ApplyID;
              ELSE
                 SELECT COUNT(*) INTO v_count2 FROM wf_applyarchive WHERE applyid = p_ApplyID;
                 IF v_count2>0 THEN--EWF 60.123歷史
                   SELECT signrecordid,signorder,processid,paresignrecordid,substart
                     INTO v_signrecordid,v_SignOrder,v_sub_processid,v_paresignrecordid,v_substart
                     FROM WF_signrecord_archive
                    WHERE applyid=p_applyID AND signorder=(
                          select max(signorder) from WF_signrecord_archive
                           where applyid=p_ApplyID AND paresignrecordid=-1  AND MUSTSIGN='1' and isrealsign='1'
                          ) and isrealsign='1' AND paresignrecordid=-1 AND rownum=1;
                     --SELECT cancancel INTO v_cancancel FROM applyarchive WHERE applyid=p_ApplyID;
                   SELECT ap.cancancel,f.contenttb INTO v_cancancel,v_contenttb FROM WF_applyarchive ap,WF_forms f
                   WHERE ap.processcode=f.processcode AND ap.applyid=p_ApplyID;
                 
                    END IF;
              end if;
              IF v_cancancel='0' THEN
                 raise_application_error(-20000,'此單據結案後不允許取回！');
              END IF;

              --取回:只能在主流程取回,不允許在子流程取回
              --故加入paresignrecordid=-1判斷   2009-12-25  by fans
              IF v_count >0 THEN
                  SELECT COUNT(*) INTO v_count1 FROM wf_signrecord_his
                  where applyid=p_ApplyID AND signorder = v_SignOrder AND
                        isrealsign = '1' AND paresignrecordid=-1 AND
                        (signatory=p_UserID or actsignatory=p_UserID);
                 IF v_count1 = 0 THEN
                   select COUNT(*) into v_count1
                     from wf_signrecord_his vs,--查詢代理人
                          (select * from vie_authorizes where rtrim(grantee)=p_UserID) aa
                    where (rtrim(aa.granter)=vs.signatory or rtrim(aa.granter)=vs.actsignatory)
                          and vs.APPLYID=p_applyID AND signorder = v_SignOrder AND
                          isrealsign = '1' and paresignrecordid=-1 AND rownum=1;
                END IF;
             ELSIF v_count2>0 THEN
                SELECT COUNT(*) INTO v_count1
                  FROM wf_signrecord_archive
                 where applyid=p_ApplyID AND signorder = v_SignOrder AND
                       isrealsign = '1' AND paresignrecordid=-1 AND
                       (signatory=p_UserID or actsignatory=p_UserID);
                 IF v_count1 = 0 THEN
                   select COUNT(*) into v_count1
                     from wf_signrecord_archive vs, --查詢代理人
                          (select * from vie_authorizes where rtrim(grantee)=rtrim(p_UserID)) aa
                    where (rtrim(aa.granter)=vs.signatory or rtrim(aa.granter)=vs.actsignatory)
                          and vs.APPLYID=p_applyID AND signorder = v_SignOrder AND
                          isrealsign = '1' and paresignrecordid=-1 and rownum=1;
                END IF;
             end if;

             IF v_count1 = 0 THEN--取得取回簽核點發生錯誤
                 Rollback;
                 P_EXECRESULT:='N';
                 raise_application_error(-20000,'無法正確獲取当前操作人員信息,請確認您不是在子流程中取回！');
                 
                 COMMIT;
                 RETURN;
             END IF;

             IF v_count2 > 0 THEN
                INSERT INTO wf_apply (FACT_NO,PROCESSID,PROCESSCODE,APPLIER,APPLYDATE,CURRENTSTATUS,
                       PREFERENTIAL,KEY1,KEY2,KEY3,APPTITLE,APPLY_NO,PRIORITY,KEY4,KEY5,KEY6,
                       LKEY,CANCANCEL,WATERPIC,KEY9,KEY10,KEY11,KEY7,KEY8,ENDDATE,CURSIGNER,
                       APPLIERNAME,CURSIGNERNM,HASFILE,CONTROLS)
                SELECT FACT_NO,PROCESSID,PROCESSCODE,APPLIER,APPLYDATE,CURRENTSTATUS,
                       PREFERENTIAL,KEY1,KEY2,KEY3,APPTITLE,APPLY_NO,PRIORITY,KEY4,KEY5,KEY6,
                       LKEY,CANCANCEL,WATERPIC,KEY9,KEY10,KEY11,KEY7,KEY8,ENDDATE,CURSIGNER,
                       APPLIERNAME,CURSIGNERNM,HASFILE,CONTROLS
                  FROM wf_applyarchive WHERE  applyid = p_ApplyID ;--RETURNING applyid INTO v_tempapplyid;
                SELECT MAX(applyid) INTO v_tempapplyid FROM wf_apply;
                UPDATE wf_apply SET applyid = p_ApplyID WHERE applyid =v_tempapplyid;
                INSERT INTO wf_applyd SELECT * FROM wf_applydarchive WHERE applyid = p_ApplyID;
                DELETE wf_applydarchive WHERE applyid=p_ApplyID;
                IF v_contenttb IS NOT NULL AND v_contenttb<>' ' THEN
                    EXECUTE immediate 'INSERT INTO wf_applyd SELECT * FROM '||v_contenttb||' WHERE applyid = '||p_ApplyID;--多表分攤內容表--2008-12-13 fans
                    EXECUTE IMMEDIATE 'delete '||v_contenttb||' where applyid='||p_ApplyID;
                END IF;

                insert into wf_Signrecord_his(SIGNRECORDID,FACT_NO,PROCESSID,PRCSTEPID,
                    APPLYID,SIGNATORY,ACTSIGNATORY,STARTTIME,ENDTIME,ISINCREMENT,ISREDIRECT,
                    BACK_MK,RESULT,SIGNFILEPATH,MEMO,SIGNTYPE,GRANTEE,DATACHGTIME,STEPNAME,
                    SIGNORDER,SIGNMODE,IP,MAC,SGUID,ISRESIGN,ISREALSIGN,USERNAME,FORMRULEID,
                    MODISIGNER,NDISPLAY,MUSTSIGN,YLEVEL,Paresignrecordid,Signbefore,Substart,
                    Userconmids,Readmk,Wf_Code,Nodetype,Remoteid,Aud_Code,Actsigner_Positnm,Step_Action)
               SELECT SIGNRECORDID,FACT_NO,PROCESSID,
                    PRCSTEPID,APPLYID,SIGNATORY,ACTSIGNATORY,STARTTIME,ENDTIME,ISINCREMENT,ISREDIRECT,
                    BACK_MK,RESULT,SIGNFILEPATH,MEMO,SIGNTYPE,GRANTEE,DATACHGTIME,STEPNAME,
                    SIGNORDER,SIGNMODE,IP,MAC,SGUID,ISRESIGN,ISREALSIGN,USERNAME,FORMRULEID,MODISIGNER,
                    NDISPLAY,MUSTSIGN,YLEVEL,Paresignrecordid,Signbefore,Substart,
                    Userconmids,Readmk,Wf_Code,Nodetype,Remoteid,Aud_Code,Actsigner_Positnm,Step_Action
                from wf_signrecord_archive where applyid=p_ApplyID;


                DELETE wf_signrecord_archive where applyid=p_ApplyID;
                --DELETE applydarchive where applyid=p_ApplyID;
                DELETE wf_applyarchive where applyid=p_ApplyID;
              END IF;

              select apply_no,CURRENTSTATUS,PROCESSID
                into v_ApplyNO,V_CURRENTSTATUS,V_PROCESSID
                from wf_apply where applyid=p_ApplyID;
              v_statttime :=SYSDATE;

              --插入主流程
              insert into wf_Signrecord(Fact_no,processid,prcstepid,applyid,signatory,Actsignatory,starttime,endtime,result, --from signrecord_his
                     isincrement,isRedirect,Back_MK,Stepname,Signorder,Signmode,sguid,Signfilepath,Username,formruleid,
                     modisigner,NDISPLAY,Mustsign,YLEVEL,Paresignrecordid,Signbefore,Substart,Userconmids,Readmk,Wf_Code,Nodetype,Remoteid,Aud_Code,Actsigner_Positnm,Step_Action)
              select fact_no,processid,prcstepid,applyid,signatory,
                     decode(signorder,v_SignOrder,'',Actsignatory) as Actsignatory,
                     decode(signorder,v_SignOrder,v_statttime,starttime) as starttime,
                     endtime,decode(signorder,v_SignOrder,'A',result) as result,
                     isincrement,isRedirect,
                     decode(signorder,v_SignOrder,'Y','N') as Back_MK,Stepname,Signorder,Signmode,ROWID
                     ,decode(signorder,v_SignOrder,'',Signfilepath) as Signfilepath
                     ,username,formruleid,modisigner,NDISPLAY,Mustsign,YLEVEL,
                     Paresignrecordid,Signbefore,Substart,Userconmids,Readmk,Wf_Code,Nodetype,Remoteid,Aud_Code,Actsigner_Positnm,Step_Action
              from wf_signrecord_his where applyid=p_ApplyID AND
                   isrealsign = '1' AND paresignrecordid=-1;
              --插入子流程
              insert into wf_Signrecord(Fact_no,processid,prcstepid,applyid,signatory,Actsignatory,starttime,endtime,result, --from signrecord_his
                     isincrement,isRedirect,Back_MK,Stepname,Signorder,Signmode,sguid,Signfilepath,Username,formruleid,
                     modisigner,NDISPLAY,Mustsign,YLEVEL,Paresignrecordid,Signbefore,Substart,Userconmids,Readmk,Wf_Code,Nodetype,Remoteid,Aud_Code,Actsigner_Positnm,Step_Action)
              select fact_no,processid,prcstepid,applyid,signatory,
                     Actsignatory,starttime,
                     endtime,result,
                     isincrement,isRedirect,
                     Back_MK,Stepname,Signorder,Signmode,ROWID
                     ,Signfilepath
                     ,username,formruleid,modisigner,NDISPLAY,Mustsign,YLEVEL,
                     (
                     SELECT s1.signrecordid
                       FROM wf_signrecord_his s1,wf_signrecord_his s2
                      WHERE s2.applyid=sr.applyid AND s2.signrecordid=sr.paresignrecordid AND
                            s1.applyid=s2.applyid AND s1.processid=s2.processid AND
                            s1.signorder=s2.signorder AND s1.endtime IS NULL
                     ) AS paresignrecordId,Signbefore,Substart,Userconmids,Readmk,Wf_Code,Nodetype,Remoteid,Aud_Code,Actsigner_Positnm,Step_Action
                from wf_signrecord_his sr
               where applyid=p_ApplyID AND isrealsign = '1' AND paresignrecordid<>-1;
              UPDATE wf_signrecord SET endtime = NULL,result='A',signfilepath=NULL
               WHERE applyid = p_ApplyID AND signorder = v_SignOrder AND paresignrecordid=-1;
              --SELECT signatory FROM signrecord WHERE
              --刪除主流程
              DELETE wf_signrecord_his
               WHERE applyid=p_ApplyID AND signorder < v_SignOrder AND
                     isrealsign = '1' AND paresignrecordid=-1;
              --刪除子流程
              DELETE wf_signrecord_his
               WHERE applyid=p_ApplyID AND
                     isrealsign = '1' AND paresignrecordid<>-1;
              UPDATE wf_signrecord_his SET endtime=v_statttime,isrealsign = '0',
                     Result='3',memo=memo||v_addMemo
               WHERE applyid=p_ApplyID AND signorder = v_SignOrder ;

              update wf_apply a
              set a.currentstatus='300.006',a.enddate ='',
              a.cursigner = p_UserID,a.CURSIGNERNM=fun_get_username(p_UserID)
              where applyid=p_ApplyID;

              P_EXECRESULT:='Y';
              --計數
              --PRO_ENGIN_OUT_INTERFACE(P_APPLYID,v_processid,P_USERID,V_CURRENTSTATUS,'300.006','');
              --commit;

              Exception
              when others then
                   Rollback;
                   P_EXECRESULT:='N';
                   p_ExecCase:=sqlerrm;
                    raise_application_error(-20000,p_ExecCase);
                 PRO_WRITE_LOGS_SMP(p_applyid,p_SignRecordID,trim(p_UserID),
                       'EWF系統結案后取回發生錯誤:'||p_ExecCase,'Sign',
                       '【PRO_ENGINE_SIGNGETBACK_FILISH】'||p_applyid||','||p_SignRecordID||
                       ','||trim(p_UserID), 'EWF');
                 --COMMIT;
                 end;

            end;";
        #endregion

    }
}
